﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XemlUtilities.ISA.Ontology;

namespace XemlUtilities.ISA.Protocol
{
    class GcMs : ProtocolBase
    {
        #region constructors
        public GcMs() : base() {
        }

        public GcMs(String Name)
            : base()
        {
            this.Name = Name;
            this.Type = Name;
            this.Register();
        }

        public static void InitProtocols(Guid xemlId, string connectionString)
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            using (cmd.Connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                cmd.CommandText = @"
--declare @xemlId uniqueidentifier;
--set @xemlId = '10b38aaf-b977-4950-85b8-f4775f66658d';

select distinct
ISNULL(isaP.alt_name, gcP.name) as protocol, pTerm.termId as protocol_accession, pOntology.name as protocol_ontology,
ISNULL(isaParam.alt_name, tTerm.name) as parameter, isaParam.id, tTerm.termId parameter_accession, tOntology.acronym parameter_ontology,
ISNULL(vTerm.name, pd.[value freetext]) AS value, vTerm.termId value_accession, vOntology.acronym value_ontology,
isaP.id

FROM ISA.Protocol AS isaP
LEFT JOIN gc.Protocol AS gcP on gcP.id = isaP.gcprotocol_id
LEFT JOIN obo.Term AS pTerm ON pTerm.id = gcP.FK_Term
LEFT JOIN obo.Ontology pOntology ON pOntology.id = pTerm.FK_Ontology
LEFT JOIN ISA.Parameter AS isaParam ON isaParam.protocol_id = isaP.id
LEFT JOIN gc.ProtocolDescription pd ON isaParam.gcprotocoldescription_id = pd.id
LEFT JOIN obo.Term AS vTerm ON vTerm.id = pd.FK_Value
LEFT JOIN obo.Ontology vOntology ON vOntology.id = vTerm.FK_Ontology
LEFT JOIN obo.Term AS tTerm ON tTerm.id = pd.FK_Term
LEFT JOIN obo.Ontology tOntology ON tOntology.id = tTerm.FK_Ontology

JOIN GC_Chromatogram AS chromatogram ON chromatogram.FK_Method = pd.FK_Method
JOIN xeml.SampleMapping AS sm ON sm.Value = chromatogram.name
JOIN xeml.Document AS document ON document.Id = sm.FK_Document

WHERE document.XemlId = @xemlId
AND sm.SubKey = 'Chromatogram'
ORDER BY isaP.id";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@xemlId", xemlId);

                try
                {
                    cmd.Connection.Open();
                    cmd.Transaction = cmd.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                    ProtocolBase curProtocol = null;
                    using (SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                    {
                        while (reader.Read())
                        {
                            String curProtocolName = (String)reader["protocol"];

                                if (curProtocol == null || curProtocol.Name != curProtocolName)
                                {
                                    curProtocol = new GcMs(curProtocolName);
                                    try
                                    {
                                        curProtocol.AccessionNr = OntologyBase.GetOrCreateTerm(
                                            ontologyAcronym: (String)reader["parameter_ontology"],
                                            termAccessionNr: (String)reader["parameter_accession"],
                                            termName:        (String)reader["parameter"]
                                        );
                                    }
                                    // that's fine, then we just don't have any value ontology information
                                	catch (System.Data.SqlTypes.SqlNullValueException e) { }
                                	catch (InvalidCastException ie) { }
                                }

                                Parameter curParameter = new Parameter();
                                curParameter.Name = (String)reader["parameter"];
                                curParameter.Value = (String)reader["value"];
                                try
                                {
                                    curParameter.AccessionNr = OntologyBase.GetOrCreateTerm(
                                        ontologyAcronym: (String)reader["value_ontology"],
                                    	termAccessionNr: (String)reader["value_accession"]
                                    );
                                }

                                // that's fine, then we just don't have any value ontology information
                                catch (System.Data.SqlTypes.SqlNullValueException e) { }
                                catch (InvalidCastException ie) { }
                                curProtocol.AddParameter(curParameter);
                        }
                    }
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }
        #endregion constructors
    }
}
